#coding=utf-8
#LightCam oracle监控程序
#init by hfh 20150319

import multiprocessing,subprocess,threading
import socket,os,time,re,traceback,sys
import lc_libs,cx_Oracle,pymongo

def func(this):
	global errorValue
	agent_hostname=this['agent_hostname']
	dbname=this['dbname']
	sqlname=this['sqlname']
	alertflag=this['alertflag']
	interval=this['interval']
	severity=this['severity']
	schedule=this['schedule']
	holiday=this['holiday']
	style=this['type']
	
	try:
		Node=agent_hostname
		AlertGroup="MonitorOracle"
		AlertKey=dbname+"-"+sqlname
		Summary=""
		Severity=0
		Type=0
		
		if style == 'sql':
			sqlfile=open("../sql/"+sqlname+".sql",'r')
			sqlstr=sqlfile.read()
			dbcon=None
			try:
				#dbcon=cx_Oracle.connect("dbmonitor/tool_pwd_0522@"+dbname)
				dbcon=cx_Oracle.connect("imonitor/imonitor@"+dbname)
				cursor=dbcon.cursor()
				cursor.execute(sqlstr)
				AlertKey=dbname
				Summary=dbname+" can be connected "
				Severity = 1
				Type=2
				strs=lc_libs.lc_gen_alert(agent_hostname,Node,AlertGroup,AlertKey,Severity,Type,Summary)
				print strs
				db.update_eventsdb(events_table,agent_hostname,Node,AlertGroup,AlertKey,Severity,Type,Summary)
			except Exception:
				excinfo=traceback.format_exc()
				print excinfo
				logdate=time.strftime('%b %d %H:%M:%S',time.localtime())
				logfile.write(logdate+" :\n"+"sqlname is: "+sqlname+"\nerror infor: "+excinfo+"\n")
				logfile.flush()
				errorValue=1
				AlertKey=dbname
				Summary=dbname+" cannot be connected "
				Severity = 5
				Type=1
				strs=lc_libs.lc_gen_alert(agent_hostname,Node,AlertGroup,AlertKey,Severity,Type,Summary)
				logfile.write(strs)
				logfile.flush()
				print strs
				#print errorValue
				db.update_eventsdb(events_table,agent_hostname,Node,AlertGroup,AlertKey,Severity,Type,Summary)
				exit()


		if sqlname == 'config':
			configMonini=lchome+'/conf/'+'ConfigMon.ini.linux.oracle'
			for lines in open(configMonini):
				if re.match("^file_name",lines):
					record=lines.strip("\r\n").split(" ")
					dir=record[1]
					files=record[2].split(",")
					for file in files:
						if (os.path.exists(dir+file)):
							filestat=os.stat(dir+file)
							t=time.localtime(filestat.st_mtime)
							mtime=str(t.tm_year)+"/"+str(t.tm_mon)+"/"+str(t.tm_mday)+" "+str(t.tm_hour)+":"+str(t.tm_min)+":"+str(t.tm_sec)
							Time=time.strftime('%Y-%m-%d %H:%M:%S',time.localtime())
							expr={'Type':sqlname,'Agent':agent_hostname,'NAME':file}
							datas={'Type':sqlname,'Agent':agent_hostname,'NAME':file,'MTIME':mtime,'UID':str(filestat.st_uid),'GID':str(filestat.st_gid),'PERMISSION':oct(filestat.st_mode)[-3:],'SIZE':filestat.st_size,'Status':'1000','Time':Time}
							print datas
							sortcont=[("Agent",pymongo.ASCENDING),("NAME",pymongo.ASCENDING)]
							saveData(expr,datas,sortcont,sqlname)
				
		if sqlname == 'disk':
			cmd="df -P|grep ' /'|awk '{print $NF\" \"$(NF-1)}'"
			popen=subprocess.Popen(cmd,stdin=subprocess.PIPE,stdout=subprocess.PIPE,stderr=subprocess.PIPE,shell=True)
			for lines in popen.stdout.readlines():
				filesystem=lines.strip("\r\n").split(" ")[0]
				used=lines.strip("\r\n").split(" ")[1]
				usedpercent=int(used.strip("%"))
				if usedpercent >=70:
					status="异常"
				else:
					status="正常"
				Time=time.strftime('%Y-%m-%d %H:%M:%S',time.localtime())
				expr={'Type':sqlname,'Agent':agent_hostname,'NAME':filesystem}
				datas={'Type':sqlname,'Agent':agent_hostname,'NAME':filesystem,'USED':used,'Percent':usedpercent,'Status':status,'Time':Time}
				print datas
				sortcont=[("Agent",pymongo.ASCENDING),("NAME",pymongo.ASCENDING)]
				saveData(expr,datas,sortcont,sqlname)

		if sqlname == 'process':
			number={}
			cmd='ps -fu oracle'
			popen=subprocess.Popen(cmd,stdin=subprocess.PIPE,stdout=subprocess.PIPE,stderr=subprocess.PIPE,shell=True)
			result=str(popen.stdout.readlines())
			number['ora_ckpt']=result.count("ora_ckpt")
			number['ora_dbw']=result.count("ora_dbw")
			number['ora_lgwr']=result.count("ora_lgwr")
			number['ora_pmon']=result.count("ora_pmon")
			number['ora_reco']=result.count("ora_reco")
			number['ora_smon']=result.count("ora_smon")
			number['tnslsnr']=result.count("tnslsnr")
			status='UP'
			for item in number:
				if number[item] <1 :
					status='DOWN'
				Time=time.strftime('%Y-%m-%d %H:%M:%S',time.localtime())
				expr={'Type':sqlname,'Agent':agent_hostname,'NAME':item}
				datas={'Type':sqlname,'Agent':agent_hostname,'NAME':item,'NUMBER':number[item],'Status':status,'Time':Time}
				print datas
				sortcont=[("Agent",pymongo.ASCENDING),("NAME",pymongo.ASCENDING)]
				saveData(expr,datas,sortcont,sqlname)
		
		if sqlname == 'tablespace':
			for line in cursor.fetchall():
				sid=line[0].strip(' ')
				name=line[1].strip(' ')
				total=line[2].strip(' ')
				used=line[3].strip(' ')
				free=line[4].strip(' ')
				freepercent=float(line[5].strip(' '))
				AlertKey=sid+"_"+name
				Summary=sid+"表空间"+name+"mb,当前大小为"+total+"mb,已使用"+used+"mb,空闲"+free+",空闲百分比为:"+str(freepercent)+"%."
				
				if freepercent < float(alertflag):
					Summary=sid+"表空间"+name+"空闲百分比为:"+str(freepercent)+"%,小于设定的阈值"+str(alertflag)+"%."
					Severity = 4
					Type=1
				else:
					Summary=sid+"表空间"+name+"空闲百分比为:"+str(freepercent)+"%,大于设定的阈值"+str(alertflag)+"%."
					Severity = 1
					Type=2
				
				strs=lc_libs.lc_gen_alert(agent_hostname,Node,AlertGroup,AlertKey,Severity,Type,Summary)
				print strs
				db.update_eventsdb(events_table,agent_hostname,Node,AlertGroup,AlertKey,Severity,Type,Summary)
		
				Time=time.strftime('%Y-%m-%d %H:%M:%S',time.localtime())
				expr={'Type':sqlname,'Agent':agent_hostname,'SID':sid,'Name':name}
				datas={'Type':sqlname,'Agent':agent_hostname,'SID':sid,'Name':name,'Total':total,'Free':free,'Time':Time}
				sortcont=[("Agent",pymongo.ASCENDING),("SID",pymongo.ASCENDING),("Name",pymongo.ASCENDING)]
				saveData(expr,datas,sortcont,sqlname)	
					
		if sqlname == 'readonly':
			for line in cursor.fetchall():
				sid=line[0].strip(' ')
				name=line[1].strip(' ')
				status=line[2].strip(' ')
				AlertKey=sid+"_"+name+"_readonly"
				
				if status =="READ ONLY":
					Summary=sid+"表空间"+name+"为只读模式"
					Severity = 4
					Type=1
				else:
					Summary=sid+"表空间"+name+"状态正常"
					Severity = 1
					Type=2
				
				strs=lc_libs.lc_gen_alert(agent_hostname,Node,AlertGroup,AlertKey,Severity,Type,Summary)
				print strs
				db.update_eventsdb(events_table,agent_hostname,Node,AlertGroup,AlertKey,Severity,Type,Summary)
		
				Time=time.strftime('%Y-%m-%d %H:%M:%S',time.localtime())
				expr={'Type':sqlname,'Agent':agent_hostname,'SID':sid,'Name':name}
				datas={'Type':sqlname,'Agent':agent_hostname,'SID':sid,'Name':name,'Status':status,'Time':Time}
				sortcont=[("Agent",pymongo.ASCENDING),("SID",pymongo.ASCENDING),("Name",pymongo.ASCENDING)]
				saveData(expr,datas,sortcont,sqlname)

					
		if sqlname == 'tablespacestatus':
			tmp=cursor.fetchall()
			if len(tmp)==0:
				print dbname+"表空间无异常数据"
			else:
				for line in tmp:
					TABLE=line[0].strip(' ')
					OWNER=line[1].strip(' ')
					TABLE_NAME=line[2].strip(' ')
					TABLESPACE_NAME=line[3].strip(' ')
					PARNAME=line[4].strip(' ')
					AlertKey=TABLESPACE_NAME+"_"+TABLE_NAME+"_tbsSpecial"
					Summary=sid+"表空间"+name+"有异常数据输出: TABLE为"+TABLE+", TABLESPACE_NAME"+TABLESPACE_NAME+"TABLE_NAME为"+TABLE_NAME
					Severity = 1
					Type=2
				
					strs=lc_libs.lc_gen_alert(agent_hostname,Node,AlertGroup,AlertKey,Severity,Type,Summary)
					print strs
					db.update_eventsdb(events_table,agent_hostname,Node,AlertGroup,AlertKey,Severity,Type,Summary)
		
					Time=time.strftime('%Y-%m-%d %H:%M:%S',time.localtime())
					expr={'Type':sqlname,'TABLE':TABLE,'TABLE_NAME':TABLE_NAME,'TABLESPACE_NAME':TABLESPACE_NAME}
					datas={'Type':sqlname,'TABLE':TABLE,'TABLE_NAME':TABLE_NAME,'TABLESPACE_NAME':TABLESPACE_NAME,'Time':Time}
					sortcont=[("Agent",pymongo.ASCENDING),("SID",pymongo.ASCENDING),("Name",pymongo.ASCENDING)]
					saveData(expr,datas,sortcont,sqlname)
								
		if sqlname == 'instancestatus':
			for line in cursor.fetchall():
				hostname=line[0].strip(' ')
				sid=line[1].strip(' ')
				version=line[2].strip(' ')
				active_state=line[3].strip(' ')
				starttime=line[4].strip(' ')
				status=line[5].strip(' ')
				AlertKey=sid
				if status == 'OPEN':
					Summary=sid+"实例状态为"+status+",启动时间为"+starttime
					Severity = 1
					Type=2
				else:
					Summary=sid+"实例状态为"+status+",启动时间为"+starttime
					Severity = 4
					Type=1
				strs=lc_libs.lc_gen_alert(agent_hostname,Node,AlertGroup,AlertKey,Severity,Type,Summary)
				print strs
				db.update_eventsdb(events_table,agent_hostname,Node,AlertGroup,AlertKey,Severity,Type,Summary)
		
				Time=time.strftime('%Y-%m-%d %H:%M:%S',time.localtime())
				expr={'Type':sqlname,'Agent':agent_hostname,'SID':sid}
				datas={'Type':sqlname,'Agent':agent_hostname,'SID':sid,'Version':version,'ActiveState':active_state,'Status':status,'Starttime':starttime,'Time':Time}
				sortcont=[("Agent",pymongo.ASCENDING),("SID",pymongo.ASCENDING)]
				saveData(expr,datas,sortcont,sqlname)

		if sqlname == 'datafiles':
			for line in cursor.fetchall():
				FILE_NAME=line[0].strip(' ')
				TABLESPACE_NAME=line[1].strip(' ')
				ONLINE_STATUS=line[2].strip(' ')
				AlertKey="DATAFILE-"+FILE_NAME.split('/')[-1]
				if ONLINE_STATUS == 'ONLINE' or ONLINE_STATUS == 'SYSTEM':
					Summary=FILE_NAME+"文件状态正常，为 "+ONLINE_STATUS
					Severity = 1
					Type=2
				else:
					Summary=FILE_NAME+"文件状态异常，为 "+ONLINE_STATUS
					Severity = 4
					Type=1
				strs=lc_libs.lc_gen_alert(agent_hostname,Node,AlertGroup,AlertKey,Severity,Type,Summary)
				print strs
				db.update_eventsdb(events_table,agent_hostname,Node,AlertGroup,AlertKey,Severity,Type,Summary)
		
				Time=time.strftime('%Y-%m-%d %H:%M:%S',time.localtime())
				expr={'Type':sqlname,'Agent':agent_hostname,'FILENAME':FILE_NAME}
				datas={'Type':sqlname,'Agent':agent_hostname,'FILENAME':FILE_NAME,'TABLESPACE_NAME':TABLESPACE_NAME,'Status':ONLINE_STATUS,'Time':Time}
				sortcont=[("Agent",pymongo.ASCENDING),("FILENAME",pymongo.ASCENDING)]
				saveData(expr,datas,sortcont,sqlname)

		if sqlname == 'controlfile':
			for line in cursor.fetchall():
				NAME=line[0].strip(' ')
				STATUS=line[1].strip(' ')
				AlertKey="CONTROLFILE-"+NAME.split('/')[-1]
				if STATUS == 'NORMAL':
					Summary=NAME+"文件状态正常"
					Severity = 1
					Type=2
				else:
					Summary=NAME+"文件状态异常，为 " + STATUS
					Severity = 4
					Type=1
				strs=lc_libs.lc_gen_alert(agent_hostname,Node,AlertGroup,AlertKey,Severity,Type,Summary)
				print strs
				db.update_eventsdb(events_table,agent_hostname,Node,AlertGroup,AlertKey,Severity,Type,Summary)
		
				Time=time.strftime('%Y-%m-%d %H:%M:%S',time.localtime())
				expr={'Type':sqlname,'Agent':agent_hostname,'NAME':NAME}
				datas={'Type':sqlname,'Agent':agent_hostname,'NAME':NAME,'Status':STATUS,'Time':Time}
				sortcont=[("Agent",pymongo.ASCENDING),("NAME",pymongo.ASCENDING)]
				saveData(expr,datas,sortcont,sqlname)
				
		if sqlname == 'crs_stat':
			cmd='/oracle/crs/bin/crs_stat'
			popen=subprocess.Popen(cmd,stdin=subprocess.PIPE,stdout=subprocess.PIPE,stderr=subprocess.PIPE,shell=True)
			AlertKey=""
			Summary=""
			Severity=1
			Type=2
			Summary2=""
			data_type=''
			data_target=''
			data_state=''
			data_host=''
			data_name=''
			for lines in popen.stdout.readlines():
				lines=lines.strip('\r\n')
				if lines == '':
					Time=time.strftime('%Y-%m-%d %H:%M:%S',time.localtime())
					expr={'Type':sqlname,'Agent':agent_hostname,'NAME':data_name}
					datas={'Type':sqlname,'Agent':agent_hostname,'NAME':data_name,'TYPE':data_type,'TARGET':data_target,'STATE':data_state,'HOST':data_host,'Time':Time}
					print datas
					sortcont=[("Agent",pymongo.ASCENDING),("NAME",pymongo.ASCENDING)]
					saveData(expr,datas,sortcont,sqlname)
					data_type=''
					data_target=''
					data_state=''
					data_host=''
					data_name=''
					continue
				line=lines.split('=')
				Summary=Summary+line[0]+": "+line[1]+" "
				if line[0]== 'NAME':
					AlertKey=line[1]
					data_name=line[1]
					
				if line[0]== 'TYPE':
					data_type=line[1]
					
				if line[0]== 'TARGET':
					data_target=line[1]
					
				if (line[0]== 'TARGET' and 'ONLINE' not in lines) or (line[0]== 'STATE' and 'ONLINE' not in lines):
					Summary2=AlertKey + "状态异常，详细如下  "
					Severity=4
					Type=1
				else:
					Summary2=AlertKey + "状态正常，详细如下  "
				if line[0]== 'STATE':
					data_state=line[1].split(" ")[0]
					data_host=line[1].split(" ")[2]
					AlertKey="crs_stat" + AlertKey
					Summary=Summary2+Summary
					strs=lc_libs.lc_gen_alert(agent_hostname,Node,AlertGroup,AlertKey,Severity,Type,Summary)
					print strs
					db.update_eventsdb(events_table,agent_hostname,Node,AlertGroup,AlertKey,Severity,Type,Summary)
		
					AlertKey=""
					Summary=""
					Summary2=""
					Severity=1
					Type=2
				
	
			 	
	except Exception:
		excinfo=traceback.format_exc()
		print excinfo
		logdate=time.strftime('%b %d %H:%M:%S',time.localtime())
		logfile.write(logdate+" :\n"+excinfo+"\n")
		logfile.flush()
		time.sleep(1)

def saveData(expr,datas,sortcont,tablename):
	table=db.UseCollection(events_db,tablename)
	opts=db.queryData(table,expr,sortcont,0)
	res=db.genResult(opts)
	if len(res)==0:db.insertDatas(table,datas)
	else:db.updateData(table,expr,datas)

if __name__ == "__main__":

	cmd='echo $LIGHTCAM_HOME'
	popen=subprocess.Popen(cmd,stdin=subprocess.PIPE,stdout=subprocess.PIPE,stderr=subprocess.PIPE,shell=True)
	while True:
		if popen.poll()==None:
			time.sleep(0.1)
			continue
		else:
			break
	lchome=popen.stdout.read().strip('\r\n')
	scname=sys.argv[0].split('.')[0]
	logfilename=lchome+'/log/'+scname+'.log'
	confname=lchome+'/conf/'+scname+'.conf'
	logfile=file(logfilename,'a')
	logdate=time.strftime('%Y-%m-%d %H:%M:%S',time.localtime())
	logfile.write(logdate+" Started.\n")
	logfile.flush()
	
	agent_hostname=lc_libs.lc_gethostname()

	try:
		#初始化mongo数据库
		
		db=lc_libs.lc_mongodb_operater('127.0.0.1',27017)

		db.connect()
		Conn=db.getConn()
		if Conn==None:
			print "Can not Connect to the MondbDB"
			exit(0)
		
		events_dbname='lc_status'
		events_tablename='lc_events'
		oramon_tablename='lc_oramon'
		
		events_db=db.useDatabase(events_dbname)
		events_table=db.UseCollection(events_db,events_tablename)
		oramon_table=db.UseCollection(events_db,oramon_tablename)

	except Exception:
		excinfo=traceback.format_exc()
		logdate=time.strftime('%b %d %H:%M:%S',time.localtime())
		logfile.write(logdate+" 初始化mongo数据库失败:\n"+excinfo+"\n")
		logfile.flush()
		exit(0)

	#主程序
	MonitorList = []
	errorValue=0
	
	for lines in open(confname):
		if lines[0] == '#' or lines.split("\t")[0] not in (agent_hostname,'default'):
			continue

		line=lines.strip('\r\n').split('\t')
		dconf={}
		
		for t in line[2].split(","):
			dconf['agent_hostname']=line[0]
			dconf['dbname']=line[1]
			dconf['sqlname']=t
			dconf['alertflag']=line[3]
			dconf['interval']=line[4]
			dconf['severity']=line[5]
			dconf['schedule']=line[6]
			dconf['holiday']=line[7]
			dconf['type']=line[8]
			MonitorList.append(dconf)
			dconf={}

	timeflag=int(time.time())
	
	while True:
		time.sleep(1)
		timeflag+=1
		realtime=int(time.time())
		#print realtime
		
		logdate=time.strftime('%b %d %H:%M:%S',time.localtime())
		
		threads = []
		
		for i in MonitorList:
			holiday_state = lc_libs.lc_isholiday()
			if int(i['holiday']) == 1 and holiday_state == True:
				continue
				
			time_state = lc_libs.lc_schedule(i['schedule'])
			if time_state == False:
				continue
			
			flag=realtime%int(i['interval'])
			if flag<=realtime-timeflag:
				#print i
				t = threading.Thread(target=func,args=(i,))
				threads.append(t)

		timeflag=int(time.time())
		for t in threads:
#			print "before "+str(errorValue)
			t.start()
#			print "after "+str(errorValue) 
			if errorValue==1:
				errorValue=0
				exit()
			while True:
				#print len(threading.enumerate())
				time.sleep(0.1)
				if(len(threading.enumerate()) < 10):
					break
